package cn.yangliu.jpa.handler;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import javax.sql.DataSource;

import cn.yangliu.jpa.annotations.JpaQuery;
import cn.yangliu.jpa.annotations.JpaSqlMapping;
import cn.yangliu.jpa.enums.DbTypeEnum;
import cn.yangliu.jpa.enums.LinkTypeEnum;
import cn.yangliu.jpa.enums.QueryTypeEnum;
import cn.yangliu.jpa.function.ParamTransformer;
import cn.yangliu.jpa.function.ValueTransformer;
import cn.yangliu.jpa.tools.JpaUtils;
import lombok.Cleanup;
import lombok.Getter;
import lombok.Setter;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

/**
 * @author 杨柳
 * @date 2019-06-15
 */
@Slf4j
class SqlHandler {

    private SqlHandler() {
    }

    private static final ParamTransformer PARAM_TRANSFORMER = (column, fieldName, value) -> {

        if (Objects.equals(DbTypeEnum.ORACLE, getDbType())) {
            if (value instanceof LocalDateTime) {
                value = Timestamp.valueOf((LocalDateTime) value);
            } else if (value instanceof LocalDate) {
                LocalDateTime temp = LocalDateTime.of((LocalDate) value, LocalTime.MIN);
                value = Timestamp.valueOf(temp);
            } else if (value instanceof Date) {
                value = Timestamp.valueOf(DateFormatUtils.format((Date) value, "yyyy-MM-dd HH:mm:sss"));
            } else if (value instanceof java.sql.Date) {
                value = Timestamp.valueOf(DateFormatUtils.format((Date) value, "yyyy-MM-dd HH:mm:sss"));
            }
        }
        return value;
    };

    private static final ValueTransformer VALUE_TRANSFORMER = (column, fieldType, value) -> {
        if (value instanceof Timestamp) {
            Timestamp timestamp = (Timestamp) value;
            if (Objects.equals(fieldType, LocalDateTime.class)) {
                value = timestamp.toInstant().atOffset(ZoneOffset.of("+8")).toLocalDateTime();
            } else if (Objects.equals(fieldType, LocalDate.class)) {
                value = timestamp.toInstant().atOffset(ZoneOffset.of("+8")).toLocalDate();
            } else if (Objects.equals(fieldType, LocalTime.class)) {
                value = timestamp.toInstant().atOffset(ZoneOffset.of("+8")).toLocalTime();
            } else if (Objects.equals(fieldType, Date.class)) {
                value = new Date(timestamp.getTime());
            } else if (Objects.equals(fieldType, java.sql.Date.class)) {
                value = new java.sql.Date(timestamp.getTime());
            }
        } else if (value instanceof BigDecimal) {
            BigDecimal decimal = (BigDecimal) value;
            if (Objects.equals(fieldType, Integer.class)) {
                value = decimal.intValue();
            }
            if (Objects.equals(fieldType, Long.class)) {
                value = decimal.longValue();
            }
            if (Objects.equals(fieldType, Float.class)) {
                value = decimal.floatValue();
            }
            if (Objects.equals(fieldType, Double.class)) {
                value = decimal.doubleValue();
            }
        }
        return value;

    };

    @Getter
    @Setter
    static class PageInfo {

        private int page;
        private int size;

        private int total = 0;

        private Sort sort = Sort.unsorted();

        int getOffset() {
            return this.page * this.size;
        }

        PageInfo(int page, int size, Sort sort) {
            this.page = page;
            this.size = size;
            this.sort = sort;
        }

    }

    /**
     * 通过动态sql 实现复杂查询并映射
     *
     * @param dataSource 数据源
     * @param sourceSql  源sql
     * @param queryPojo  查询条件封装
     * @param clazz      返回结果类型
     * @param <T>        泛型
     * @return 单条数据
     */
    public <T> T queryForObjectBySql(DataSource dataSource, String sourceSql, Object queryPojo, Class<T> clazz) {
        return queryForObjectBySql(dataSource, sourceSql, queryPojo, clazz, PARAM_TRANSFORMER, VALUE_TRANSFORMER);
    }

    /**
     * 通过动态sql 实现复杂查询并映射
     *
     * @param dataSource       数据源
     * @param sourceSql        源sql
     * @param queryPojo        查询条件封装
     * @param clazz            返回结果类型
     * @param paramTransformer 参数转换器
     * @param valueTransformer 结果转换器
     * @param <T>              泛型
     * @return 单条数据
     */
    @SneakyThrows
    static <T> T queryForObjectBySql(DataSource dataSource, String sourceSql, Object queryPojo, Class<T> clazz,
                                     ParamTransformer paramTransformer, ValueTransformer valueTransformer) {
        @Cleanup Connection connection = dataSource.getConnection();
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        buidSql(sourceSql, queryPojo, null, sql, null, params, paramTransformer);

        @Cleanup PreparedStatement ps = connection.prepareStatement(sql.toString());
        doLog(params, sql.toString());
        @Cleanup ResultSet resultSet = getResultSet(ps, null, params, null);
        T data = clazz.newInstance();
        analysisResultSet(resultSet, clazz, data, valueTransformer);
        JpaUtils.THREAD_LOCAL.remove();
        return data;
    }


    /**
     * 构建sql
     *
     * @param sourceSql        源sql
     * @param queryPojo        查询条件封装
     * @param page             分页信息
     * @param querySql         查询sql
     * @param countSql         统计sql
     * @param params           参数
     * @param paramTransformer 参数转换器
     */
    @SneakyThrows
    private static void buidSql(String sourceSql, Object queryPojo, PageInfo page, StringBuilder querySql,
                                StringBuilder countSql, List<Object> params, ParamTransformer paramTransformer) {

        boolean doPage = Objects.nonNull(page);
        doPageStart(sourceSql, querySql, countSql, doPage);

        querySql.append(sourceSql);
        if (Objects.isNull(queryPojo)) {
            doPageEnd(page, querySql, countSql, doPage);
            return;
        }

        if (Objects.isNull(paramTransformer)) {
            paramTransformer = PARAM_TRANSFORMER;
        }
        boolean sorted = false;
        if (Objects.nonNull(page)) {
            sorted = page.sort.isSorted();
        }


        List<String> orders = new ArrayList<>();
        List<String> directions = new ArrayList<>();
        if (sorted) {
            Iterator<Sort.Order> iterator = page.sort.iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                String property = order.getProperty();
                orders.add(property);
                String direction = order.getDirection().name();
                directions.add(direction);
            }
        }
        buildConditions(queryPojo, querySql, countSql, params, paramTransformer, doPage,
                orders);

        String orderBy = buildOrderBy(sorted, orders, directions);
        querySql.append(orderBy);
        if (doPage) {
            countSql.append(orderBy);
        }

        doPageEnd(page, querySql, countSql, doPage);
    }


    /**
     * 构建排序
     *
     * @param sorted     是否排序
     * @param orders     排序字段
     * @param directions 排序字段倒序还是逆序
     * @return 排序语句
     */
    private static String buildOrderBy(boolean sorted, List<String> orders, List<String> directions) {
        if (!sorted) {
            return "";
        }
        StringBuilder sb = new StringBuilder(" order by ");

        for (int i = 0; i < orders.size(); i++) {
            String property = orders.get(i);
            String direction = directions.get(i);
            sb.append(property).append(" ").append(direction);
            if ((i + 1) != orders.size()) {
                sb.append(",");
            }
        }

        return sb.toString();
    }

    /**
     * 构建条件
     *
     * @param queryPojo        参数封装
     * @param querySql         查询sql
     * @param countSql         统计sql
     * @param params           参数集合
     * @param paramTransformer 参数转换器
     * @param doPage           是否分页
     */
    private static void buildConditions(Object queryPojo, StringBuilder querySql, StringBuilder countSql,
                                        List<Object> params, ParamTransformer paramTransformer, boolean doPage,
                                        List<String> sortFields) {
        Field[] declaredFields = queryPojo.getClass().getDeclaredFields();


        boolean whereFlag = false;
        for (Field declaredField : declaredFields) {

            Object value = JpaUtils.getFieldValue(queryPojo, declaredField);

            String fieldName = declaredField.getName();

            if (Objects.isNull(value) || Objects.equals(value, "")) {
                continue;
            }

            JpaQuery jpaQuery = declaredField.getDeclaredAnnotation(JpaQuery.class);

            if (Objects.isNull(jpaQuery)) {
                continue;
            }

            LinkTypeEnum linkType = jpaQuery.link();

            String column = getColumn(declaredField, jpaQuery);
            if (sortFields.contains(fieldName)) {
                int index = sortFields.indexOf(fieldName);
                sortFields.remove(fieldName);
                sortFields.add(index, column);

            }

            value = paramTransformer.transform(column, declaredField.getName(), value);

            QueryTypeEnum queryTypeEnum = jpaQuery.type();
            if (!whereFlag) {
                querySql.append(JpaUtils.WHERE);
                if (doPage) {
                    countSql.append(JpaUtils.WHERE);
                }

            }

            querySql.append(" ").append(JpaUtils.linkType(linkType, whereFlag)).append(" ");

            if (JpaUtils.isNotEmptyArray(value)) {
                querySql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ");
                JpaUtils.buildArrayParams(querySql, value);
            } else {
                if (Objects.equals(QueryTypeEnum.LIKE, queryTypeEnum)) {
                    value = "%" + value.toString() + "%";
                }
                params.add(value);
                querySql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ").append("?");
            }


            if (doPage) {
                countSql.append(" ").append(JpaUtils.linkType(linkType, whereFlag)).append(" ");
                if (JpaUtils.isNotEmptyArray(value)) {
                    countSql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ");
                    JpaUtils.buildArrayParams(countSql, value);
                } else {
                    countSql.append(column).append(" ").append(queryTypeEnum.symbol()).append(" ").append("?");
                }

            }
            whereFlag = true;


        }
    }


    /**
     * 获取字段
     *
     * @param declaredField 属性
     * @param jpaQuery      属性上的JpaQuery注解
     * @return SQL语句中的字段
     */
    private static String getColumn(Field declaredField, JpaQuery jpaQuery) {
        String column = jpaQuery.column();
        if (StringUtils.isBlank(column)) {
            column = JpaUtils.humpToUnderline(declaredField.getName()).toUpperCase();
        }

        String tableAlias = jpaQuery.tableAlias();
        if (!StringUtils.isBlank(tableAlias)) {
            column = tableAlias + "." + column;
        }
        return column;
    }

    /**
     * 分页的前头语句
     *
     * @param sourceSql 原sql
     * @param querySql  查询sql
     * @param countSql  统计sql
     * @param doPage    是否分页
     */
    private static void doPageStart(String sourceSql, StringBuilder querySql, StringBuilder countSql, boolean doPage) {
        if (doPage) {
            if (Objects.equals(DbTypeEnum.ORACLE, getDbType())) {
                querySql.append("select * from (select ROWNUM rn,TEMPTABLE.* FROM (");
            }
            countSql.append("select COUNT(*) COUNT from (");
            countSql.append(sourceSql);
        }
    }

    /**
     * 判断是不是分页查询
     *
     * @param page     分页参数
     * @param querySql 查询sql
     * @param countSql 统计sql
     * @param doPage   是否做分页
     */
    private static void doPageEnd(PageInfo page, StringBuilder querySql, StringBuilder countSql, boolean doPage) {
        if (doPage) {
            if (Objects.equals(DbTypeEnum.ORACLE, getDbType())) {
                querySql.append(") TEMPTABLE ) where rn > ");
                querySql.append(page.getOffset()).append(" and ").append("rn <= ").append((page.getSize() + page.getOffset()));
                countSql.append(")");
            } else {
                querySql.append(" limit ").append(page.getOffset()).append(",").append(page.getSize());
                countSql.append(") as temptable");
            }
        }
    }


    /**
     * 获取ResultSet
     *
     * @param queryPs PreparedStatement
     * @param countPs PreparedStatement
     * @param params  参数
     * @return ResultSet
     */
    @SneakyThrows
    private static ResultSet getResultSet(PreparedStatement queryPs, PreparedStatement countPs, List<Object> params,
                                          PageInfo pageInfo) {
        for (int i = 0; i < params.size(); i++) {
            Object value = params.get(i);
            queryPs.setObject((i + 1), value);
            if (Objects.nonNull(pageInfo)) {
                countPs.setObject((i + 1), value);
            }
        }

        if (Objects.nonNull(pageInfo)) {
            ResultSet resultSet = countPs.executeQuery();
            if (resultSet.next()) {
                pageInfo.total = resultSet.getInt("COUNT");
            }
        }

        return queryPs.executeQuery();
    }

    /**
     * 通过动态sql 实现复杂查询并映射
     *
     * @param dataSource 数据源
     * @param sourceSql  源sql
     * @param queryPojo  查询条件封装
     * @param clazz      返回结果类型
     * @param <T>        泛型
     * @return list数据
     */
    public static <T> List<T> queryForListBySql(DataSource dataSource, String sourceSql, Object queryPojo,
                                                Class<T> clazz) {
        return queryForListBySql(dataSource, sourceSql, queryPojo, clazz, PARAM_TRANSFORMER, VALUE_TRANSFORMER);
    }

    /**
     * 通过动态sql 实现复杂查询并映射
     *
     * @param dataSource       数据源
     * @param sourceSql        源sql
     * @param queryPojo        查询条件封装
     * @param clazz            返回结果类型
     * @param paramTransformer 参数转换器
     * @param <T>              泛型
     * @return list数据
     */
    @SneakyThrows
    public static <T> List<T> queryForListBySql(DataSource dataSource, String sourceSql, Object queryPojo,
                                                Class<T> clazz, ParamTransformer paramTransformer,
                                                ValueTransformer valueTransformer) {
        StringBuilder sql = new StringBuilder();
        @Cleanup Connection connection = dataSource.getConnection();
        List<Object> params = new ArrayList<>();
        buidSql(sourceSql, queryPojo, null, sql, null, params, paramTransformer);

        doLog(params, sql.toString());

        @Cleanup PreparedStatement ps = connection.prepareStatement(sql.toString());
        @Cleanup ResultSet resultSet = getResultSet(ps, null, params, null);

        List<T> dataList = new ArrayList<>();
        analysisResultSet(resultSet, clazz, dataList, valueTransformer);
        JpaUtils.THREAD_LOCAL.remove();
        return dataList;
    }

    /**
     * 记录带参数的日志
     *
     * @param params 参数
     * @param sql    sql语句
     */
    private static void doLog(List<Object> params, String sql) {
        int matches = StringUtils.countMatches(sql, "?");
        for (int i = 0; i < matches; i++) {
            sql = StringUtils.replaceOnce(sql, "?", params.get(i).toString());
        }
        log.info(sql);
    }

    /**
     * 通过动态sql 实现复杂查询并分页
     *
     * @param dataSource 数据源
     * @param sourceSql  源sql
     * @param queryPojo  查询条件封装
     * @param pageable   分页条件
     * @param clazz      返回结果类型
     * @param <T>        泛型
     * @return list数据
     */
    @SneakyThrows
    static <T> Page<T> queryForListBySql(DataSource dataSource, String sourceSql, Object queryPojo,
                                         Pageable pageable, Class<T> clazz) {
        return queryForListBySql(dataSource, sourceSql, queryPojo, pageable, clazz, PARAM_TRANSFORMER,
                VALUE_TRANSFORMER);
    }

    /**
     * 通过动态sql 实现复杂查询并分页
     *
     * @param dataSource       数据源
     * @param sourceSql        源sql
     * @param queryPojo        查询条件封装
     * @param pageable         分页条件
     * @param clazz            返回结果类型
     * @param paramTransformer 参数转换器
     * @param valueTransformer 值转换器
     * @param <T>              泛型
     * @return list数据
     */
    @SneakyThrows
    static <T> Page<T> queryForListBySql(DataSource dataSource, String sourceSql, Object queryPojo,
                                         Pageable pageable, Class<T> clazz, ParamTransformer paramTransformer,
                                         ValueTransformer valueTransformer) {
        PageInfo pageInfo = new PageInfo(pageable.getPageNumber(), pageable.getPageSize(), pageable.getSort());
        @Cleanup Connection connection = dataSource.getConnection();
        StringBuilder querySql = new StringBuilder();
        StringBuilder countSql = new StringBuilder();

        List<Object> params = new ArrayList<>();

        buidSql(sourceSql, queryPojo, pageInfo, querySql, countSql, params, paramTransformer);

        doLog(params, querySql.toString());
        doLog(params, countSql.toString());

        @Cleanup PreparedStatement queryPS = connection.prepareStatement(querySql.toString());
        @Cleanup PreparedStatement countPs = connection.prepareStatement(countSql.toString());
        @Cleanup ResultSet resultSet = getResultSet(queryPS, countPs, params, pageInfo);
        List<T> dataList = new ArrayList<>();
        analysisResultSet(resultSet, clazz, dataList, valueTransformer);

        return new PageImpl<>(dataList, pageable, pageInfo.total);
    }


    /**
     * 解析ResultSet
     *
     * @param resultSet        resultSet
     * @param clazz            目标实体对象class
     * @param data             数据封装对象 可能是list 也可能是 目标实体对象class的实例
     * @param valueTransformer 值转换器
     * @param <T>              泛型
     */
    @SneakyThrows
    private static <T> void analysisResultSet(ResultSet resultSet, Class<T> clazz, Object data,
                                              ValueTransformer valueTransformer) {

        if (Objects.isNull(valueTransformer)) {
            valueTransformer = VALUE_TRANSFORMER;
        }

        List<Field> fields = JpaUtils.getDeclaredFields(clazz);
        Map<String, Field> fieldMap = new HashMap<>(10);
        cacheClassFields(fields, fieldMap);
        while (resultSet.next()) {
            T t;

            if (!(data instanceof List)) {
                t = (T) data;
            } else {
                t = clazz.newInstance();
            }

            setFieldValue(resultSet, valueTransformer, fieldMap, t);
            if (data instanceof List) {
                ((List<T>) data).add(t);
            }

        }

    }

    /**
     * 遍历时缓存属性 避免多次调用反射获取对象的成员变量
     *
     * @param fields   成员变量
     * @param fieldMap 成员变量map
     */
    private static void cacheClassFields(List<Field> fields, Map<String, Field> fieldMap) {
        for (Field field : fields) {
            String column;
            JpaSqlMapping mapping = field.getDeclaredAnnotation(JpaSqlMapping.class);
            if (Objects.nonNull(mapping)) {
                if (mapping.skip()) {
                    continue;
                }
                column = mapping.column();
                if (StringUtils.isBlank(column)) {
                    column = JpaUtils.humpToUnderline(field.getName()).toUpperCase();
                }

            } else {
                column = JpaUtils.humpToUnderline(field.getName()).toUpperCase();
            }
            fieldMap.put(column, field);

        }
    }

    /**
     * 设置成员变量的值
     *
     * @param resultSet        ResultSet
     * @param valueTransformer 值转换器
     * @param fieldMap         成员变量map
     * @param t                对象
     * @param <T>              泛型
     */
    @SneakyThrows
    private static <T> void setFieldValue(ResultSet resultSet, ValueTransformer valueTransformer,
                                          Map<String, Field> fieldMap, T t) {

        ResultSetMetaData metaData = resultSet.getMetaData();

        int count = metaData.getColumnCount();

        List<String> columns = new ArrayList<>();

        for (int i = 1; i <= count; i++) {
            columns.add(metaData.getColumnName(i).toUpperCase());
        }

        for (String column : columns) {
            if (!fieldMap.containsKey(column)) {
                continue;
            }
            Object value = resultSet.getObject(column);
            if (Objects.isNull(value)) {
                continue;
            }
            Field field = fieldMap.get(column);
            value = valueTransformer.transform(column, field.getType(), value);

            JpaUtils.setFieldValue(t, field, value);
        }

    }


    private static DbTypeEnum getDbType() {
        DbTypeEnum dbTypeEnum = JpaUtils.THREAD_LOCAL.get();
        if (Objects.isNull(dbTypeEnum)) {
            dbTypeEnum = JpaUtils.defaultDbType;
        }
        return dbTypeEnum;
    }

}